package org.unt.db;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.unt.logic.Configurations;

public class Test extends Thread {

	private static Logger log = Logger.getLogger(Test.class);

	private String name;

	public Test(String name) {
		this.name = name;
	}

	public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {

		Test test1 = new Test("Test 1");
		test1.start();

		Test test2 = new Test("Test 2");
		test2.start();
	}

	public void run() {

		if ("Test 1".equals(name)) {

			try {
				select1(null);
			} catch (IOException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else if ("Test 2".equals(name)) {

			try {
				select2(null);
			} catch (IOException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void select1(String[] args) throws IOException, ClassNotFoundException, SQLException {

		log.info("Start (1)");
		long totalTime = Calendar.getInstance().getTimeInMillis();

		FileWriter out = new FileWriter("C:/Java/temp/out1.txt");

		Properties props = Configurations.load("config.properties");
		Db db = new Db(props, "mysql");

		Connection conn = db.getConnection();
		Statement statement = conn.createStatement();

		for (int i = 0; i < 2; i++) {
			String b = i < 10 ? "0" + i : "" + i;

			for (int j = 0; j < 10; j++) {
				String c = j < 10 ? "00" + j : (j < 100 ? "0" + j : "" + j);

				long time1 = Calendar.getInstance().getTimeInMillis();
				ResultSet rs = statement.executeQuery("select * from TEST.TEST where CODE2 = 'B" + b + "' and CODE3 = 'C" + c + "'");
				long time2 = Calendar.getInstance().getTimeInMillis();
				log.info("Select secs (1) " + (time2 - time1) / 1000);

				while (rs.next()) {

					int id = rs.getInt("ID");
					String descr = rs.getString("DESCR");

					out.write(id + "\t" + descr + "\n");
				}

				log.info("Write secs (1) " + (Calendar.getInstance().getTimeInMillis() - time2) / 1000);
			}
		}

		db.close();
		out.close();
		log.info("Total secs (1) " + (Calendar.getInstance().getTimeInMillis() - totalTime) / 1000);
	}

	public static void select2(String[] args) throws IOException, ClassNotFoundException, SQLException {

		log.info("Start (2)");
		long totalTime = Calendar.getInstance().getTimeInMillis();

		FileWriter out = new FileWriter("C:/Java/temp/out2.txt");

		Properties props = Configurations.load("config.properties");
		Db db = new Db(props, "mysql");

		Connection conn = db.getConnection();
		Statement statement = conn.createStatement();

		for (int i = 0; i < 2; i++) {

			String str = i < 10 ? "0" + i : "" + i;

			long time1 = Calendar.getInstance().getTimeInMillis();
			ResultSet rs = statement.executeQuery("select * from TEST.TEST where CODE2 = 'B" + str + "'");
			long time2 = Calendar.getInstance().getTimeInMillis();
			log.info("Select secs (2) " + (time2 - time1) / 1000);

			int j = 0;
			while (rs.next()) {
				j++;

				int id = rs.getInt("ID");
				String descr = rs.getString("DESCR");

				out.write(id + "\t" + descr + "\n");
			}

			log.info("Write secs (2) " + (Calendar.getInstance().getTimeInMillis() - time2) / 1000);
		}

		db.close();
		out.close();
		log.info("Total secs (2) " + (Calendar.getInstance().getTimeInMillis() - totalTime) / 1000);
	}

	public static void inserts(String[] args) throws IOException, ClassNotFoundException, SQLException {

		log.info("Start");
		long l = Calendar.getInstance().getTimeInMillis();

		Properties props = Configurations.load("config.properties");
		Db db = new Db(props, "mysql");

		Connection conn = db.getConnection();
		Statement statement = conn.createStatement();

		log.info("Iterate");
		for (int i = 0; i < 600; i++) {

			StringBuffer insert = new StringBuffer("insert into TEST.TEST values\n");
			for (int j = 0; j < 18000; j++) {

				insert.append("(default, 'A" + random(10, 2) + "','B" + random(50, 2) + "','C" + random(200, 3) + "','Descr'," + Math.random() * 100000
						+ "),\n");
			}

			insert.deleteCharAt(insert.length() - 2);

			statement.executeUpdate(insert.toString());

			log.info("" + i);
		}

		db.close();
		log.info("Secs " + (Calendar.getInstance().getTimeInMillis() - l) / 1000);
	}

	public static void test(String[] args) throws IOException, ClassNotFoundException, SQLException {

		Properties props = Configurations.load("config.properties");
		Db db = new Db(props, "mysql");

		ResultSet rs = db.select("select * from TEST.TEST");
		rs.next();
		int id = rs.getInt("ID");
		String code = rs.getString("CODE");

		log.info(id + "\t" + code);

		db.close();
	}

	public static String random(int decs, int len) {

		int randomInt = (int) (Math.random() * decs);
		String randomStr = "00000" + randomInt;
		randomStr = randomStr.substring(randomStr.length() - len, randomStr.length());

		return randomStr;
	}
}
